At 16:10 +0200 on 17/2/99, Jim Mercer wrote:
>
> > 3) Back to the issue of INSERTS - copies are faster. If you can transform
> > the data into tab-delimited format as required by COPY, you save a lot
> > of time on parsing, planning etc.
>
> this sorta defeats the purpose of putting the data in an SQL database. 8^)
You probably didn't understand me. If you convert it to tab delimited text
and then use COPY table_name FROM filename/stdin instead of INSERT, it will
be much faster, because you don't have to do the parsing and planning on
each line, but only on the whole copy.
I didn't tell you to use the data directly from those text files...
In fact, it doesn't require using text files at all, just reformatting your
program. If until now it did
- - - -
while (data_still_coming) {
sprintf( command, "INSERT INTO table1 VALUES( %s, %s, %s )",
item1, item2, item3 );
PQexec( con, command );
}
- - - -
Now you have to do instead
- - - -
PQexec( con, "COPY table1 FROM stdin" );
while (data_still_coming) {
sprintf( line, "%s\t%s\t%s\n" , item1, item2, item3 );
PQputline( con, line );
}
PQputline( con, ".\n" );
PQendcopy(con);
- - - -
It's simply a different formatting to your data insertion.
Herouth
